/**
 * 
 */
package org.mhz.sit.util;

import java.util.List;

import javafx.collections.ObservableList;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.mhz.sit.entity.BaseSpeaker;
import org.mhz.sit.entity.Settings;
import org.mhz.sit.entity.Speaker;
import org.mhz.sit.entity.SpeakerOffTopicOrder;
import org.mhz.sit.entity.SpeakerOrder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author Mahan
 *
 * Database utility class
 */
public final class DatabaseUtil
{
    private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseUtil.class);
    
    /**
     * Private constructor
     */
    private DatabaseUtil()
    {
        
    }
    
    /**
     * Get the data from a table
     *
     * @param aTableName - The table name to get the list from
     * @return - The data from the table as a list
     */
    public static List<?> getData(final String aTableName)
    {
        final Session session = HibernateUtil.getSession();
        Query query = null;
        try 
        {            
            // Create a Hibernate query (HQL)
            query = session.createQuery("FROM " + aTableName);
            
            // Retrieve all
            return  query.list();            
        }
        catch (Exception ex) 
        {
            LOGGER.debug("Could not get data from " + aTableName, ex);
            AlertUtil.displayAlert(Constants.SIT_990_COULD_NOT_RETRIEVE_DATA);
        }
        finally 
        {
            session.close();
        }   
        
        return null;
    }
    
    /**
     * Delete all the data from a table
     *
     * @param aTableName - The table name to delete from
     * @return - The data from the table as a list
     */
    public static int deleteAllData(final String aTableName)
    {
        int deleteCount = 0;
        
        final Session session = HibernateUtil.getSession();
        Transaction tx = null;
        Query query = null;
        try 
        {            
            tx = session.beginTransaction();
            
            // Create a Hibernate query (HQL)
            query = session.createQuery("DELETE FROM " + aTableName);
            deleteCount = query.executeUpdate();  
            
            tx.commit();
        }
        catch (Exception ex) 
        {
            if (tx != null)
            {
                LOGGER.debug("Rolling back transaction due to an error.", ex);
                tx.rollback();
                
                AlertUtil.displayAlert(Constants.SIT_991_ROLLBACK_OCCURRED);                
            }            
        }
        finally 
        {
            session.close();
        }   
        
        return deleteCount;
    }
    
    /**
     * Add a speaker
     *
     * @param aSpeaker - The speaker to add
     * @param aObservableList - The observable list to add the speaker to
     */
    public static void addSpeaker(final Speaker aSpeaker, 
                                  final ObservableList<Speaker> aObservableList)
    {
        final Session session = HibernateUtil.getSession();
        Transaction tx = null;
        try 
        {
            tx = session.beginTransaction();
            
            session.persist(aSpeaker);
            
            tx.commit();
            
            // If the transaction has been committed then update the list
            aObservableList.add(aSpeaker);
        }
        catch (Exception ex) 
        {
            if (tx != null)
            {
                LOGGER.debug("Rolling back transaction due to an error.", ex);
                tx.rollback();
                
                AlertUtil.displayAlert(Constants.SIT_991_ROLLBACK_OCCURRED);
            }
        }
        finally 
        {
            session.close();
        }          
    }
    
    /**
     * Add to the speaker order
     *
     * @param aSpeaker - The speaker to add
     * @param aObservableList - The observable list to add the speaker to
     */
    public static void addToSpeakerOrder(final SpeakerOrder aSpeaker, 
                                         final ObservableList<SpeakerOrder> aObservableList)
    {
        final Session session = HibernateUtil.getSession();
        Transaction tx = null;
        try 
        {
            tx = session.beginTransaction();
                        
            session.persist(aSpeaker);
            
            tx.commit();
            
            // If the transaction has been committed then update the list
            aObservableList.add(aSpeaker);
        }
        catch (Exception ex) 
        {
            if (tx != null)
            {
                LOGGER.debug("Rolling back transaction due to an error.", ex);
                tx.rollback();
                
                AlertUtil.displayAlert(Constants.SIT_991_ROLLBACK_OCCURRED);
            }
        }
        finally 
        {
            session.close();
        }          
    }
    
    /**
     * Update a speaker
     *
     * @param aSpeaker - The speaker to update
     */
    public static void updateSpeaker(final Speaker aSpeaker)
    {
        final Session session = HibernateUtil.getSession();
        Transaction tx = null;
        try 
        {
            tx = session.beginTransaction();
            
            session.update(aSpeaker);
            
            tx.commit();
        }
        catch (Exception ex) 
        {
            if (tx != null)
            {
                LOGGER.debug("Rolling back transaction due to an error.", ex);
                tx.rollback();
                
                AlertUtil.displayAlert(Constants.SIT_991_ROLLBACK_OCCURRED);
            }
        }
        finally 
        {
            session.close();
        }          
    }
    
    /**
     * Delete a speaker
     *
     * @param aSpeaker - The speaker to delete
     * @param aObservableList - The observable list to delete the speaker from
     */
    public static void deleteSpeaker(final Speaker aSpeaker, 
                                     final ObservableList<Speaker> aObservableList)
    {
        final Session session = HibernateUtil.getSession();
        Transaction tx = null;
        try 
        {
            tx = session.beginTransaction();
            
            session.delete(aSpeaker);
            
            tx.commit();
            
            // If the transaction has been committed then update the list
            aObservableList.remove(aSpeaker);
        }
        catch (Exception ex) 
        {
            if (tx != null)
            {
                LOGGER.debug("Rolling back transaction due to an error.", ex);
                tx.rollback();
                
                AlertUtil.displayAlert(Constants.SIT_991_ROLLBACK_OCCURRED);
            }
        }
        finally 
        {
            session.close();
        }          
    } 
    
    /**
     * Delete a speaker from the order
     *
     * @param aSpeaker - The speaker to delete
     * @param aObservableList - The observable list to delete the speaker from
     */
    public static void deleteSpeakerOrder(final SpeakerOrder aSpeaker, 
                                          final ObservableList<SpeakerOrder> aObservableList)
    {
        final Session session = HibernateUtil.getSession();
        Transaction tx = null;
        try 
        {
            tx = session.beginTransaction();
            
            session.delete(aSpeaker);
            
            tx.commit();
            
            // If the transaction has been committed then update the list
            aObservableList.remove(aSpeaker);
        }
        catch (Exception ex) 
        {
            if (tx != null)
            {
                LOGGER.debug("Rolling back transaction due to an error.", ex);
                tx.rollback();
                
                AlertUtil.displayAlert(Constants.SIT_991_ROLLBACK_OCCURRED);
            }
        }
        finally 
        {
            session.close();
        }          
    }
    
    /**
     * Move the speaker to another list
     *
     * @param aSpeaker - The speaker to move
     * @param aObservableSpeakerOrderList - The speaker order list
     * @param aObservableOffTopicOrderList - The off topic list
     */
    public static void moveSpeakerTo(final BaseSpeaker aSpeaker,
                                     final ObservableList<SpeakerOrder> aObservableSpeakerOrderList,
                                     final ObservableList<SpeakerOffTopicOrder> aObservableOffTopicOrderList)
    {
        final Session session = HibernateUtil.getSession();
        Transaction tx = null;
        try 
        {
            tx = session.beginTransaction();
            
            if (aSpeaker instanceof SpeakerOffTopicOrder)
            {
                final SpeakerOffTopicOrder speakerOffTopic = (SpeakerOffTopicOrder) aSpeaker;
                final SpeakerOrder speakerOrder = new SpeakerOrder(speakerOffTopic.getSpeakerId(), 
                                                                   speakerOffTopic.getName());
                
                // remove the speaker from the off topic table
                session.delete(speakerOffTopic);
                
                // Add the speaker to the speaker order table
                session.persist(speakerOrder);
                
                tx.commit();
                
                // If the transaction has been committed then update the lists
                aObservableOffTopicOrderList.remove(speakerOffTopic); 
                aObservableSpeakerOrderList.add(speakerOrder);
            }
            else if (aSpeaker instanceof SpeakerOrder)
            {                
                final SpeakerOrder speakerOrder = (SpeakerOrder) aSpeaker;                
                final SpeakerOffTopicOrder speakerOffTopic = 
                        new SpeakerOffTopicOrder(speakerOrder.getSpeakerId(), 
                                                 speakerOrder.getName());                
                
                // remove the speaker from the speaker order table
                session.delete(speakerOrder);
                
                // Add the speaker to the speaker off topic table
                session.persist(speakerOffTopic); 
                
                tx.commit();
                
                // If the transaction has been committed then update the lists
                aObservableSpeakerOrderList.remove(speakerOrder); 
                aObservableOffTopicOrderList.add(speakerOffTopic);              
            }                        
        }
        catch (Exception ex) 
        {
            if (tx != null)
            {
                LOGGER.debug("Rolling back transaction due to an error.", ex);
                tx.rollback();
                
                AlertUtil.displayAlert(Constants.SIT_991_ROLLBACK_OCCURRED);
            }
        }
        finally 
        {
            session.close();
        }          
    }
    
    /**
     * Add a speaker
     *
     * @param aSpeaker - The speaker to add
     * @param aObservableList - The observable list to add the speaker to
     */
    public static void saveOrUpdateSettings(final List<Settings> aSettings)
    {
        final Session session = HibernateUtil.getSession();
        Transaction tx = null;
        try 
        {
            tx = session.beginTransaction();
            
            for (Settings settings : aSettings)
            {
                session.saveOrUpdate(settings);
            }
            
            tx.commit();
        }
        catch (Exception ex) 
        {
            if (tx != null)
            {
                LOGGER.debug("Rolling back transaction due to an error.", ex);
                tx.rollback();
                
                AlertUtil.displayAlert(Constants.SIT_991_ROLLBACK_OCCURRED);
            }
        }
        finally 
        {
            session.close();
        }          
    }    
}
